using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using VisualDB.Entities;
using System.Collections.Generic;
using MySql.Data.MySqlClient;
using System.Diagnostics;
using Gizmox.WebGUI.Forms;

namespace VisualDB.DAL
{
    public class MySQLDAL : BaseDAL, IDatabaseDAL
    {
        private DatabaseEntity _databaseEntity;
        private MySqlConnection _cnn;
        private MySqlTransaction _transaction;
        private string _connectionString;

        public MySQLDAL(DatabaseEntity databaseEntity)
        {
            _databaseEntity = databaseEntity;

            _connectionString = string.Format("Server={0};Username={1};Password={2};", databaseEntity.ServerName, databaseEntity.Username, databaseEntity.Password);
        }

        #region IDatabaseDAL Members


        public DatabaseEntity DatabaseEntity
        {
            get
            {
                return _databaseEntity;
            }

            set
            {
                _databaseEntity = value;
            }
        }

        public List<DatabaseEntity> GetDatabases(out string errors)
        {
            List<DatabaseEntity> databases = new List<DatabaseEntity>();

            errors = "";

            try
            {
                using (MySqlConnection cnn = new MySqlConnection(this._connectionString))
                {
                    cnn.Open();

                    MySqlCommand cmd = new MySqlCommand("show databases", cnn);
                    cmd.CommandType = CommandType.Text;
                    MySqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        DatabaseEntity database = new DatabaseEntity();
                        database.DatabaseType = DatabaseType.GetDatabaseType(DatabaseType.SQLServer);
                        database.ServerName = this.DatabaseEntity.ServerName;
                        database.DatabaseName = reader.GetString(0);
                        database.Username = this.DatabaseEntity.Username;
                        database.Password = this.DatabaseEntity.Password;
                        databases.Add(database);
                    }

                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                errors = ex.Message;
            }

            return databases;
        }

        public bool IsOpened
        {
            get
            {
                if (_cnn != null)
                    return (_cnn.State == ConnectionState.Open);
                else
                    return false;
            }
        }

        public void Open()
        {
            _cnn = new MySqlConnection(_connectionString);
            _cnn.Open();
            _cnn.ChangeDatabase(this.DatabaseEntity.DatabaseName);
            _cnn.InfoMessage += new MySqlInfoMessageEventHandler(_cnn_InfoMessage);
        }

        public void BeginTransaction()
        {
            _transaction = _cnn.BeginTransaction();
        }

        public void RollbackTransaction()
        {
            _transaction.Rollback();
        }

        public void CommitTransaction()
        {
            _transaction.Commit();
        }

        void _cnn_InfoMessage(object sender, MySqlInfoMessageEventArgs e)
        {
            foreach (MySqlError error in e.errors)
            {
                Debug.WriteLine(error.ToString());
            }
        }

        public void Close()
        {
            _cnn.Close();
        }

        public List<TableEntity> GetTables()
        {
            List<TableEntity> tables = new List<TableEntity>();

            MySqlCommand cmd = new MySqlCommand("Select Table_Name from INFORMATION_SCHEMA.Tables where Table_Type='BASE TABLE' and TABLE_SCHEMA=?Schema Order By Table_Name", _cnn);
            cmd.Parameters.AddWithValue("?Schema", this.DatabaseEntity.DatabaseName);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                TableEntity table = new TableEntity();
                table.Name = reader.GetString(0);
                tables.Add(table);
            }

            reader.Close();

            return tables;
        }

        public string RenameTable(string oldName, string newName)
        {
            return string.Format("RENAME TABLE '{0}' TO '{1}'", oldName, newName);
        }

        public string DeleteTable(TableEntity table)
        {
            return string.Format("DROP TABLE '{0}'", table.Name);
        }

        public string SaveTable(bool newTable, TableEntity table, List<ColumnEntity> originalColumns, List<ColumnEntity> newColumns)
        {
            return base.GenerateScriptSaveTable(DatabaseType.GetDatabaseType(DatabaseType.MySQL), newTable, table, originalColumns, newColumns, "ADD COLUMN", "CHANGE COLUMN", "DROP COLUMN");
        }

        private List<ColumnEntity> GetColumnsInternal(object tableView)
        {
            string tableViewName;

            if (tableView is TableEntity)
            {
                tableViewName = ((TableEntity)tableView).Name;
            }
            else
            {
                tableViewName = ((ViewEntity)tableView).Name;
            }

            List<ColumnEntity> columns = new List<ColumnEntity>();

            MySqlCommand cmd = new MySqlCommand("SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_SCHEMA=?Schema AND TABLE_NAME = ?TableName) ORDER BY ORDINAL_POSITION", _cnn);
            cmd.Parameters.AddWithValue("?Schema", this.DatabaseEntity.DatabaseName);
            cmd.Parameters.AddWithValue("?TableName", tableViewName);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                ColumnEntity column = new ColumnEntity();
                column.Id = Guid.NewGuid().ToString();
                column.Name = reader.GetString(0);

                string type = reader.GetString(1);
                object maxChars = reader.GetValue(2);

                if (maxChars != DBNull.Value)
                    column.Type = type + "(" + maxChars.ToString() + ")";
                else
                    column.Type = type;

                string allowNulls = reader.GetString(3);
                column.AllowNulls = allowNulls == "YES" ? true : false;

                string primaryKey = reader.GetString(4);

                column.PrimaryKey = primaryKey == "PRI" ? true : false;
                
                columns.Add(column);
            }

            reader.Close();

            return columns;
        }

        public List<ColumnEntity> GetColumns(TableEntity table)
        {
            return GetColumnsInternal(table);
        }

        public List<ColumnEntity> GetColumns(ViewEntity view)
        {
            return GetColumnsInternal(view);
        }

        public List<ViewEntity> GetViews()
        {
            List<ViewEntity> views = new List<ViewEntity>();

            MySqlCommand cmd = new MySqlCommand("Select Table_Name from INFORMATION_SCHEMA.VIEWS where (TABLE_SCHEMA=?Schema) Order By Table_Name", _cnn);
            cmd.Parameters.AddWithValue("?Schema", this.DatabaseEntity.DatabaseName);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                ViewEntity view = new ViewEntity();
                view.Name = reader.GetString(0);
                views.Add(view);
            }

            reader.Close();

            return views;
        }
        
        public string RenameView(string oldName, string newName)
        {
            string definition = GetViewDefinition(oldName);

            return string.Format("DROP VIEW IF EXISTS `{0}`;CREATE OR REPLACE VIEW `{1}` AS {2}", oldName, newName, definition);
        }

        public string DeleteView(ViewEntity view)
        {
            return string.Format("DROP VIEW '{0}'", view.Name);
        }

        private string GetViewDefinition(string viewName)
        {
            MySqlCommand cmd = new MySqlCommand("Select VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS where (TABLE_SCHEMA=?Schema AND TABLE_NAME=?ViewName)", _cnn);
            cmd.Parameters.AddWithValue("?Schema", this.DatabaseEntity.DatabaseName);
            cmd.Parameters.AddWithValue("?ViewName", viewName);

            return cmd.ExecuteScalar().ToString();
        }

        public List<StoredProcedureEntity> GetStoredProcedures()
        {
            List<StoredProcedureEntity> storedProcedures = new List<StoredProcedureEntity>();

            MySqlCommand cmd = new MySqlCommand("Select ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA=?Schema Order By ROUTINE_NAME", _cnn);
            cmd.Parameters.AddWithValue("?Schema", this.DatabaseEntity.DatabaseName);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                StoredProcedureEntity storedProcedure = new StoredProcedureEntity();
                storedProcedure.Name = reader.GetString(0);
                storedProcedures.Add(storedProcedure);
            }

            reader.Close();

            return storedProcedures;
        }

        public string RenameStoredProcedure(string oldName, string newName)
        {
            string definition = GetStoredProcedureDefinition(oldName);

            return string.Format("DROP PROCEDURE IF EXISTS `{0}`;\r\nCREATE PROCEDURE {1}() {2}", oldName, newName, definition);
        }

        public string DeleteStoredProcedure(StoredProcedureEntity storedProcedure)
        {
            return string.Format("DROP PROCEDURE '{0}'", storedProcedure.Name);
        }

        private string GetStoredProcedureDefinition(string storedProcedureName)
        {
            MySqlCommand cmd = new MySqlCommand("Select ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES where (ROUTINE_SCHEMA=?Schema AND ROUTINE_NAME=?StoredProcedureName)", _cnn);
            cmd.Parameters.AddWithValue("?Schema", this.DatabaseEntity.DatabaseName);
            cmd.Parameters.AddWithValue("?StoredProcedureName", storedProcedureName);

            return cmd.ExecuteScalar().ToString();
        }

        public DataSet ExecuteQuery(string query, out string errors)
        {
            DataSet ds = null;
            errors = "";

            try
            {
                BeginTransaction();
                ds = new DataSet();
                MySqlCommand cmd = new MySqlCommand(query, _cnn, _transaction);
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(ds);
                CommitTransaction();
            }
            catch (Exception ex)
            {
                errors = ex.Message;
                ds = null;
                RollbackTransaction();
            }

            return ds;
        }

        public int ExecuteCommand(string query, out string errors)
        {
            errors = "";
            int retVal = 0;

            try
            {
                BeginTransaction();

                MySqlCommand cmd = new MySqlCommand(query, _cnn, _transaction);
                retVal = cmd.ExecuteNonQuery();

                CommitTransaction();
            }
            catch (Exception ex)
            {
                errors = ex.Message;
                RollbackTransaction();
            }

            return retVal;
        }

        public string GenerateScriptTable(ScriptType scriptType, TableEntity table)
        {
            return GenerateScriptTable(scriptType, table, this.GetColumns(table), "?");
        }

        public string GenerateScriptAddEditView(ViewEntity view, bool newView)
        {
            if (newView)
            {
                return string.Format("CREATE VIEW '{0}' AS\r\n", view.Name);
            }
            else
            {
                string definition = GetViewDefinition(view.Name);
                return string.Format("DROP VIEW IF EXISTS `{0}`;\r\nCREATE OR REPLACE VIEW `{0}` AS {1}", view.Name, definition);
            }
        }

        public string GenerateScriptSelectView(ViewEntity view)
        {
            return base.GenerateScriptSelectView(view, this.GetColumns(view));
        }

        public string GenerateScriptAddEditStoredProcedure(StoredProcedureEntity storedProcedure, bool newStoredProcedure)
        {
            if (newStoredProcedure)
            {
                return string.Format("CREATE PROCEDURE `{0}`()\r\nBEGIN\r\nEND", storedProcedure.Name);
            }
            else
            {
                string definition = GetStoredProcedureDefinition(storedProcedure.Name);
                return string.Format("DROP PROCEDURE IF EXISTS `{0}`;\r\nCREATE PROCEDURE '{0}'() {1}", storedProcedure.Name, definition);
            }
        }

        public string GenerateScriptExecuteStoredProcedure(StoredProcedureEntity storedProcedure)
        {
            return string.Format("CALL '{0}'()", storedProcedure.Name);
        }

        #endregion
    }
}
